﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using SetSailModel;
using Dapper;
using System.Data;

namespace SetSailDal
{
    /// <summary>
    /// 登录
    /// </summary>
    public static class MainDal
    {
        #region 启航者车队读档

        /// <summary>
        /// 读取前缀名
        /// </summary>
        /// <param name="ly"></param>
        /// <returns></returns>
        public static Result<LH_CDQZM> Get_QZM()
        {
            Result<LH_CDQZM> res = new Result<LH_CDQZM>();
            try
            {
                string sql = string.Format(@"SELECT TOP 1 * FROM LH_CDQZM AS lc WHERE lc.SCBJ=0");
                using (DBHelper help = new DBHelper())
                {
                    res.Data = help.DbContext.QueryFirstOrDefault<LH_CDQZM>(sql);
                    if (res.Data == null)
                    {
                        res.Code = ResultCode.Fail;
                        res.Message = "未设置前缀名";
                    }
                }
            }
            catch (Exception ex)
            {
                res.Code = ResultCode.Exception;
                res.Message = ex.Message;
            }
            return res;
        }

        /// <summary>
        /// 设置前缀名
        /// </summary>
        /// <param name="ly"></param>
        /// <returns></returns>
        public static Result<LH_CDQZM> Set_QZM(string qzm)
        {
            Result<LH_CDQZM> res = new Result<LH_CDQZM>();
            try
            {
                string sql = string.Format(@"UPDATE LH_CDQZM SET SCBJ = 1 WHERE SCBJ=0;
                                             INSERT INTO LH_CDQZM
                                             (ID,QZM,CJSJ)
                                             VALUES
                                             (REPLACE(NEWID(),'-',''),'{0}',GETDATE())", qzm);
                using (DBHelper help = new DBHelper())
                {
                    res.Count = help.DbContext.Execute(sql);
                    if (res.Count == 0)
                    {
                        res.Code = ResultCode.Fail;
                        res.Message = "前缀名保存失败";
                    }
                }
            }
            catch (Exception ex)
            {
                res.Code = ResultCode.Exception;
                res.Message = ex.Message;
            }
            return res;
        }

        /// <summary>
        /// 保存读档记录
        /// </summary>
        /// <param name="ly"></param>
        /// <returns></returns>
        public static Result<int> Save_DDJL(string DLZH)
        {
            Result<int> res = new Result<int>();
            try
            {
                string sql = string.Format(@"INSERT INTO LH_DDJL
                                                (
                                                	DDJLID,
                                                	DDZH,
                                                	DDSJ,
                                                	SCBJ
                                                )
                                                VALUES
                                                (
                                                	REPLACE(NEWID(),'-',''),
                                                	'{0}',
                                                	GETDATE(),
                                                    0
                                                )", DLZH);
                using (DBHelper help = new DBHelper())
                {
                    res.Count = help.DbContext.Execute(sql);
                    if (res.Count == 0)
                    {
                        res.Code = ResultCode.Fail;
                        res.Message = "读档记录保存失败";
                    }
                }
            }
            catch (Exception ex)
            {
                res.Code = ResultCode.Exception;
                res.Message = ex.Message;
            }
            return res;
        }

        #endregion

        #region 启航者车队存档

        /// <summary>
        /// 保存存档记录
        /// </summary>
        /// <param name="ly"></param>
        /// <returns></returns>
        public static Result<int> Save_CDJL(string DLZH)
        {
            Result<int> res = new Result<int>();
            try
            {
                string sql = string.Format(@"INSERT INTO LH_CDJL
                                                    (
                                                    	CDJLID,
                                                    	CDZH,
                                                    	CDSJ,
                                                        SCBJ
                                                    )
                                                    VALUES
                                                    (
                                                    	REPLACE(NEWID(),'-',''),
                                                    	'{0}',
                                                    	GETDATE(),
                                                        0
                                                    )", DLZH);
                using (DBHelper help = new DBHelper())
                {
                    res.Count = help.DbContext.Execute(sql);
                    if (res.Count == 0)
                    {
                        res.Code = ResultCode.Fail;
                        res.Message = "存档记录保存失败";
                    }
                }
            }
            catch (Exception ex)
            {
                res.Code = ResultCode.Exception;
                res.Message = ex.Message;
            }
            return res;
        }

        #endregion

        #region 修改密码

        /// <summary>
        /// 修改密码
        /// </summary>
        /// <param name="ly"></param>
        /// <returns></returns>
        public static Result<int> Upd_PWD(string DLMM, string ID)
        {
            Result<int> res = new Result<int>();
            try
            {
                string sql = string.Format(@"UPDATE LH_YHXX
                                                SET DLMM = '{0}' WHERE ID='{1}'", DLMM, ID);
                using (DBHelper help = new DBHelper())
                {
                    res.Count = help.DbContext.Execute(sql);
                    if (res.Count == 0)
                    {
                        res.Code = ResultCode.Fail;
                        res.Message = "密码修改失败";
                    }
                }
            }
            catch (Exception ex)
            {
                res.Code = ResultCode.Exception;
                res.Message = ex.Message;
            }
            return res;
        }

        #endregion

        #region 后台审核

        /// <summary>
        /// 后台审核查询
        /// </summary>
        /// <param name="ly"></param>
        /// <returns></returns>
        public static Result<DataTable> Query_HTSH(string ZCZH)
        {
            Result<DataTable> res = new Result<DataTable>();
            try
            {
                string sql = string.Format(@"SELECT lz.ID,lz.ZCZH,lz.ZCMM, lz.YY, lz.QQ, lz.SQSJ,'确定' SHTG , '确定' SHBTG 
                                               FROM LH_ZCSH AS lz WITH(NOLOCK)
                                              WHERE lz.SCBJ=0 AND lz.ZCZH LIKE '%{0}%' AND ISNULL(lz.SHZT,'')=''", ZCZH);
                using (DBHelper help = new DBHelper())
                {
                    var result = help.DbContext.ExecuteReader(sql);
                    DataTable dt = new DataTable();
                    dt.Load(result);
                    res.Data = dt;
                }
            }
            catch (Exception ex)
            {
                res.Code = ResultCode.Exception;
                res.Message = ex.Message;
            }
            return res;
        }

        /// <summary>
        /// 确定审核
        /// </summary>
        /// <param name="ZCZH"></param>
        /// <returns></returns>
        public static Result<int> QDSH_HTSH(LH_ZCSH lz)
        {
            Result<int> res = new Result<int>();
            try
            {
                string sql = string.Format(@"UPDATE LH_ZCSH
                                                SET SHZT = @SHZT,
                                                 SHZH = @SHZH,
                                                 SHSJ = GETDATE()
                                                WHERE ID=@ID");
                using (DBHelper help = new DBHelper())
                {
                    res.Count = help.DbContext.Execute(sql, lz);
                    if (res.Count == 0)
                    {
                        res.Message = "审核操作异常";
                    }
                    Save_YHXX(lz);
                }
            }
            catch (Exception ex)
            {
                res.Code = ResultCode.Exception;
                res.Message = ex.Message;
            }
            return res;
        }

        /// <summary>
        /// 保存用户信息
        /// </summary>
        private static void Save_YHXX(LH_ZCSH lz)
        {
            string sql = @"INSERT INTO LH_YHXX
                                    (
                                    	ID,
                                    	DLZH,
                                    	DLMM,
                                    	ZHLX,
                                    	QQ,
                                    	YY,
                                    	CJSJ,
                                    	SCBJ
                                    )
                                    VALUES
                                    (
                                    	REPLACE(NEWID(),'-',''),
                                    	@ZCZH,
                                    	@ZCMM,
                                    	'队员',
                                    	@QQ,
                                    	@YY,
                                    	GETDATE(),
                                    	0
                                    )";
            using (DBHelper help = new DBHelper())
            {
                help.DbContext.Execute(sql, lz);
            }
        }
        #endregion

        #region 审核记录

        /// <summary>
        /// 查询审核记录
        /// </summary>
        /// <param name="ZCZH"></param>
        /// <returns></returns>
        public static Result<DataTable> Query_SHJL(string ZCZH, string SHZT)
        {
            Result<DataTable> res = new Result<DataTable>();
            try
            {
                SHZT = SHZT == "全部" ? "%" : SHZT;
                string sql = string.Format(@"SELECT lz.ZCZH,lz.SHZT,lz.SHZH,lz.SHSJ
                                               FROM LH_ZCSH AS lz WITH(NOLOCK) 
                                              WHERE lz.SCBJ=0 AND lz.ZCZH LIKE '%{0}%' AND lz.SHZT LIKE '{1}'
                                                    AND ISNULL(lz.SHZT,'')<>''", ZCZH, SHZT);
                using (DBHelper help = new DBHelper())
                {
                    var result = help.DbContext.ExecuteReader(sql);
                    DataTable dt = new DataTable();
                    dt.Load(result);
                    res.Data = dt;
                }
            }
            catch (Exception ex)
            {
                res.Code = ResultCode.Exception;
                res.Message = ex.Message;
            }
            return res;
        }
        #endregion

        #region 用户管理

        /// <summary>
        /// 查询用户信息
        /// </summary>
        /// <param name="ZCZH"></param>
        /// <returns></returns>
        public static Result<DataTable> Query_YHXX(string DLZH, string DLZT)
        {
            Result<DataTable> res = new Result<DataTable>();
            try
            {
                DLZT = DLZT == "全部" ? "%" : DLZT;
                string sql = string.Format(@"SELECT ID,DLZH,ZHLX,QQ,YY,CJSJ,DLZT,'确定' JZDL ,'确定' HFDL 
                                               FROM LH_YHXX AS ly WITH(NOLOCK)
                                              WHERE ly.SCBJ=0 AND ly.DLZH LIKE '%{0}%' AND ly.DLZT LIKE '{1}'", DLZH, DLZT);
                using (DBHelper help = new DBHelper())
                {
                    var result = help.DbContext.ExecuteReader(sql);
                    DataTable dt = new DataTable();
                    dt.Load(result);
                    res.Data = dt;
                }
            }
            catch (Exception ex)
            {
                res.Code = ResultCode.Exception;
                res.Message = ex.Message;
            }
            return res;
        }

        /// <summary>
        /// 禁止/恢复登陆
        /// </summary>
        /// <param name="DLZH"></param>
        /// <param name="DLZT"></param>
        /// <returns></returns>
        public static Result<int> Upd_JZDL(string ID, string DLZT)
        {
            Result<int> res = new Result<int>();
            try
            {

                string sql = string.Format(@"UPDATE LH_YHXX
                                                SET DLZT = '{1}' 
                                              WHERE ID='{0}'", ID, DLZT);
                using (DBHelper help = new DBHelper())
                {
                    res.Count = help.DbContext.Execute(sql);
                    if (res.Count == 0)
                    {
                        res.Code = ResultCode.Fail;
                        res.Message = "禁止失败";
                    }
                }
            }
            catch (Exception ex)
            {
                res.Code = ResultCode.Exception;
                res.Message = ex.Message;
            }
            return res;
        }

        /// <summary>
        /// 设置账号类型
        /// </summary>
        /// <param name="ID"></param>
        /// <param name="ZHLX"></param>
        /// <returns></returns>
        public static Result<int> Update_ZHLX(string ID, string ZHLX)
        {
            Result<int> res = new Result<int>();
            try
            {

                string sql = string.Format(@"UPDATE LH_YHXX
                                                SET ZHLX = '{1}' 
                                              WHERE ID='{0}'", ID, ZHLX);
                using (DBHelper help = new DBHelper())
                {
                    res.Count = help.DbContext.Execute(sql);
                    if (res.Count == 0)
                    {
                        res.Code = ResultCode.Fail;
                        res.Message = "设置账号权限失败，请联系管理员";
                    }
                    else
                    {
                        res.Message = "已设置该账号为：" + ZHLX;
                    }
                }
            }
            catch (Exception ex)
            {
                res.Code = ResultCode.Exception;
                res.Message = ex.Message;
            }
            return res;
        }
        #endregion

        #region 存档记录

        /// <summary>
        /// 查询存档记录
        /// </summary>
        /// <param name="Curr"></param>
        /// <param name="PageSize"></param>
        /// <param name="CDZH"></param>
        /// <returns></returns>
        public static Result<DataTable> Query_CDJL(int Curr, int PageSize, string CDZH)
        {
            Result<DataTable> res = new Result<DataTable>();
            try
            {
                string sql = string.Format(@"SELECT row_number() OVER (ORDER BY t.CDSJ DESC) RowNum, t.CDJLID,t.CDZH,t.CDSJ,'确定' SCCD
                                               FROM (SELECT row_number() OVER (ORDER BY lc.CDSJ DESC) RowNum,* 
                                                       FROM LH_CDJL AS lc WITH(NOLOCK)
                                                      WHERE lc.SCBJ=0 AND lc.CDZH LIKE '{2}%') t
                                              WHERE t.RowNum>{1}*{0} AND t.RowNum<={1}*({0}+1)
                                           ORDER BY t.CDSJ DESC", Curr, PageSize, CDZH);
                string sql_size = string.Format(@"SELECT CASE WHEN COUNT(1)%{0}=0 THEN COUNT(1)/{0} ELSE COUNT(1)/{0}+1 END
                                                    FROM LH_CDJL AS lc WITH(NOLOCK) 
                                                   WHERE lc.SCBJ=0 AND lc.CDZH LIKE '{1}%'", PageSize, CDZH);
                using (DBHelper help = new DBHelper())
                {
                    var result = help.DbContext.ExecuteReader(sql);
                    DataTable dt = new DataTable();
                    dt.Load(result);
                    res.Data = dt;
                    res.Count = (int)help.DbContext.ExecuteScalar(sql_size);
                }
            }
            catch (Exception ex)
            {
                res.Code = ResultCode.Exception;
                res.Message = ex.Message;
            }
            return res;
        }

        /// <summary>
        /// 删除存档记录
        /// </summary>
        /// <param name="DLZH"></param>
        /// <param name="DLZT"></param>
        /// <returns></returns>
        public static Result<int> Upd_CDJL(string ID)
        {
            Result<int> res = new Result<int>();
            try
            {
                string sql = string.Format(@"UPDATE LH_CDJL
                                                SET SCBJ =1
                                              WHERE CDJLID='{0}'", ID);
                using (DBHelper help = new DBHelper())
                {
                    res.Count = help.DbContext.Execute(sql);
                    if (res.Count == 0)
                    {
                        res.Code = ResultCode.Fail;
                        res.Message = "删除存档失败";
                    }
                }
            }
            catch (Exception ex)
            {
                res.Code = ResultCode.Exception;
                res.Message = ex.Message;
            }
            return res;
        }

        #endregion

        #region 读档记录

        /// <summary>
        /// 查询读档记录
        /// </summary>
        /// <param name="Curr"></param>
        /// <param name="PageSize"></param>
        /// <param name="DDZH"></param>
        /// <returns></returns>
        public static Result<DataTable> Query_DDJL(int Curr, int PageSize, string DDZH)
        {
            Result<DataTable> res = new Result<DataTable>();
            try
            {
                string sql = string.Format(@"SELECT row_number() OVER (ORDER BY t.DDSJ DESC) RowNum, t.DDJLID,t.DDZH,t.DDSJ,'确定' SCDD
                                               FROM (SELECT row_number() OVER (ORDER BY lc.DDSJ DESC) RowNum,* 
                                                       FROM LH_DDJL AS lc WITH(NOLOCK)
                                                      WHERE lc.SCBJ=0 AND lc.DDZH LIKE '{2}%') t
                                              WHERE t.RowNum>{1}*{0} AND t.RowNum<={1}*({0}+1)
                                           ORDER BY t.DDSJ DESC", Curr, PageSize, DDZH);
                string sql_size = string.Format(@"SELECT CASE WHEN COUNT(1)%{0}=0 THEN COUNT(1)/{0} ELSE COUNT(1)/{0}+1 END
                                                    FROM LH_DDJL AS lc WITH(NOLOCK) 
                                                   WHERE lc.SCBJ=0 AND lc.DDZH LIKE '{1}%'", PageSize, DDZH);
                using (DBHelper help = new DBHelper())
                {
                    var result = help.DbContext.ExecuteReader(sql);
                    DataTable dt = new DataTable();
                    dt.Load(result);
                    res.Data = dt;
                    res.Count = (int)help.DbContext.ExecuteScalar(sql_size);
                }
            }
            catch (Exception ex)
            {
                res.Code = ResultCode.Exception;
                res.Message = ex.Message;
            }
            return res;
        }

        /// <summary>
        /// 删除读档记录
        /// </summary>
        /// <param name="DLZH"></param>
        /// <param name="DLZT"></param>
        /// <returns></returns>
        public static Result<int> Upd_DDJL(string ID)
        {
            Result<int> res = new Result<int>();
            try
            {
                string sql = string.Format(@"UPDATE LH_DDJL
                                                SET SCBJ =1
                                              WHERE DDJLID='{0}'", ID);
                using (DBHelper help = new DBHelper())
                {
                    res.Count = help.DbContext.Execute(sql);
                    if (res.Count == 0)
                    {
                        res.Code = ResultCode.Fail;
                        res.Message = "删除读档失败";
                    }
                }
            }
            catch (Exception ex)
            {
                res.Code = ResultCode.Exception;
                res.Message = ex.Message;
            }
            return res;
        }
        #endregion
    }
}
